# -*- encoding:utf-8 -*-
# @Date  :2018-10-31
# @Author:田国新（tianguoxin#antiy.cn）

# 角色同步

import json
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib.validform import V, ValidateForm
from lib.funcs import validateAnguan

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 添加用户信息
@app.route('/api/sync/user/add', methods=['POST'])
def add_user():
    from lib import funcs
    md5_word = funcs.calc_md5(config.defaultPassword)
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            person_id=['用户id', V.required(),V.maxlength(30)],
            person_type=['用户类型', V.required()],
            person_name=['用户姓名', V.required(),V.maxlength(30)],
            user_name=[
                '账号',
                V.required(),
                V.rangelength(3, 20),
                V.pattern(r'^[a-zA-Z0-9_]+$', '由字母数字下划线组成')
            ],
            node_id=['机构', V.required(), V.maxlength(30)],
            user_group_id=['用户组id', V.optional(None)], # 数组
            email=['邮箱地址', V.optional(''), V.email(), V.maxlength(50)],
            mobile_phone=[
                '手机号码',
                V.optional(''),
                V.pattern('^1[0-9]{10}$', '正确的手机号格式'),
                V.maxlength(50)
            ],
            remark=['备注', V.optional(), V.maxlength(50)],
            r_person=['创建人', V.required(), V.maxlength(30)],
        )

        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        # 0 外部，1 内部
        if data['person_type'] not in ['0', '1']:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'person_type',
                    'message': '不存在'
                }
            })
        data["login_id"] = data["person_id"]
        # data['user_group_id']=json.dumps(data['user_group_id'])
        g.cursor.execute(
            """select person_id from sys_person where person_id =%(person_id)s and state = '1'""",
            (data))
        person_id = g.cursor.fetchone()
        g.cursor.execute(
            """select login_id from sys_login where login_id =%(login_id)s and state = '1'""",
            (data))
        login_id = g.cursor.fetchone()
        g.cursor.execute(
            """select user_name from sys_login where user_name =%(user_name)s and state = '1'""",
            (data))
        user_name = g.cursor.fetchone()

        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(node_id)s and state = '1'""",
            (data))
        agency_id = g.cursor.fetchone()

        user_group_id = data['user_group_id']

        if person_id is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'person_id',
                    'message ': '重复'
                }
            })

        if login_id is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'login_id',
                    'message ': '重复'
                }
            })

        if user_name is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'user_name',
                    'message ': '重复'
                }
            })

        if agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'node_id',
                    'message ': '不存在'
                }
            })
        if len(user_group_id) > 0:
            for items in user_group_id:
                g.cursor.execute(
                    """select user_group_id from sys_user_group where user_group_id ='%s' and state = '1'"""
                    % (items))
                isInsert = g.cursor.fetchone()
                if isInsert is None:
                    return json.dumps({
                        'status': 'E3',
                        'msg': {
                            'errorKey': 'user_group_id',
                            'message': '不存在'
                        }
                    })
        """校验创建人ID"""
        g.cursor.execute("""select person_id,person_name
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        r_person = g.cursor.fetchone()
        if not r_person:
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "新增用户 创建人不存在"}})
        else:
            g.cursor.execute(
            """insert into sys_person(person_type,person_name,agency_id,person_id,email,mobile_phone,remark,state,data_source,r_person,update_time)
                values('%s','%s','%s',
                '%s','%s','%s','%s',1,0,'%s',now())""" %
            (data['person_type'], data['person_name'], data['node_id'],
             data['person_id'], data['email'],
             data['mobile_phone'], data['remark'], r_person['person_name']))
            g.cursor.execute(
                """insert into sys_login(user_name,person_id,login_id,user_password,data_source)
                values('%s','%s','%s','%s','0')""" %
                (data['user_name'], data['person_id'],
                data['login_id'], md5_word))
            if len(user_group_id) == 0: 
                g.cursor.execute("""INSERT into h_login_synchroniz_info (login_id,person_id_change,user_name_change,user_node_change,email_change,phoneno_change) 
                                        select %(user_name)s as login_id,%(person_id)s as person_id_change,%(person_name)s as user_name_change,a.agency_name as user_node_change,
                                        %(email)s as email_change,%(mobile_phone)s as phoneno_change
                                        from sys_agency_info a
                                        where a.agency_id = %(node_id)s""" ,(data))
            else:
                for itme in user_group_id:
                    g.cursor.execute(
                        """insert into sys_login_group(login_id,user_group_id,data_source)
                                        values('%s','%s','0')""" %
                        (data['login_id'], itme))
                g.cursor.execute("""INSERT into h_login_synchroniz_info (login_id,person_id_change,user_name_change,user_group_change,user_node_change,email_change,phoneno_change) 
                                    select '%s' as login_id,'%s' as person_id_change,b.user_group_change,
                                    '%s' as user_name_change,a.agency_name as user_node_change,'%s' as email_change,
                                    '%s' as phoneno_change
                                    from (select '%s'::text as per_id,agency_name,agency_id from sys_agency_info) a
                                    left join (select '%s' as per_id,array_to_string(array_agg(user_group_name),',') as user_group_change from sys_user_group
                                                where user_group_id in (select unnest (array%s))
                                                group by per_id) b on a.per_id = b.per_id
                                    where a.agency_id = '%s'""" %(data['user_name'],data['person_id'],data['person_name'],data['email'],
                                    data['mobile_phone'],data['person_id'],data['person_id'],json.dumps(data['user_group_id']).replace("\"","'"),data['node_id']))
            g.cursor.execute(
                """insert into sys_user_theme (person_id,left_navigation,head_navigation,fixed_footer,theme_colour,r_person)
                                values('%s','0','0','1','blue','%s')""" %
                (data['person_id'], r_person['person_name']))
            g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({"status": "E0", 'msg': '新增用户失败', 's':str(e)})


#  修改用户
@app.route('/api/sync/user/update', methods=['POST'])
def user_update():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            person_name=['用户姓名', V.required(), V.maxlength(30)],
            node_id=['机构', V.required(), V.maxlength(30)],
            user_group_id=['用户组id', V.optional(None)],
            person_id=['用户id', V.required(), V.maxlength(30)],
            email=['邮箱地址', V.optional(''), V.email(), V.maxlength(50)],
            mobile_phone=[
                '手机号码',
                V.optional(''),
                V.pattern('^1[0-9]{10}$', '正确的手机号格式'),
                V.maxlength(50)
            ],
            remark=['备注', V.optional(), V.maxlength(50)],
            r_person=['记录人', V.required(), V.maxlength(30)])

        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        # data['user_group_id']=json.dumps(data['user_group_id'])
        g.cursor.execute(
            """select person_id from sys_login where person_id =%(person_id)s and state = '1'""",
            (data))
        person_id = g.cursor.fetchone()

        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(node_id)s and state = '1'""",
            (data))
        agency_id = g.cursor.fetchone()

        user_group_id = data['user_group_id']

        if person_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'person_id',
                    'message ': '不存在'
                }
            })

        if agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'agency_id',
                    'message ': '不存在'
                }
            })
        if len(user_group_id) > 0:
            for items in user_group_id:
                g.cursor.execute(
                    """select user_group_id from sys_user_group where user_group_id ='%s' and state = '1'"""
                    % (items))
                isInsert = g.cursor.fetchone()
                if isInsert is None:
                    return json.dumps({
                        'status': 'E3',
                        'msg': {
                            'errorKey': 'user_group_id',
                            'message': '不存在'
                        }
                    })
        """校验记录人ID"""
        g.cursor.execute("""select person_id,person_name
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        r_person = g.cursor.fetchone()
        if not r_person:
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "修改用户 记录人不存在"}})
        else:
            data['r_person'] =r_person['person_name']
            g.cursor.execute(
                """update sys_person set email = %(email)s,person_name = %(person_name)s,r_person=%(r_person)s,
                mobile_phone = %(mobile_phone)s, remark = %(remark)s, agency_id = %(node_id)s,update_time=now()
                where  person_id = %(person_id)s""", (data))
            g.cursor.execute(
                """
                    select login_id from sys_login where person_id = %(person_id)s
                """, data)
            login_id = g.cursor.fetchone()['login_id']
            g.cursor.execute("""
                    delete from sys_login_group where login_id = '%s'
                """ % login_id)
            if len(user_group_id) == 0:
                g.cursor.execute("""INSERT into h_login_synchroniz_info (login_id,person_id_change,user_name_change,user_node_change,email_change,phoneno_change) 
                                    select b.user_name as login_id,%(person_id)s as person_id_change,%(person_name)s as user_name_change,a.agency_name as user_node_change,
                                    %(email)s as email_change,%(mobile_phone)s as phoneno_change
                                    from (select a.agency_name,a.agency_id,%(person_id)s ::text as per_id from sys_agency_info a where a.agency_id = %(node_id)s) a
                                    left join (select a.user_name,%(person_id)s ::text as per_id from sys_login a where a.person_id = %(person_id)s) b on a.per_id = b.per_id
                                    where a.agency_id = %(node_id)s""" ,(data))
            else:
                for items in data['user_group_id']:
                    g.cursor.execute(
                        """insert into sys_login_group(login_id,user_group_id,data_source)
                                        values('%s','%s','1')""" % (login_id, items))
                g.cursor.execute("""INSERT into h_login_synchroniz_info (login_id,person_id_change,user_name_change,user_group_change,user_node_change,email_change,phoneno_change) 
                                    select (select user_name from sys_login where state='1' and person_id = '%s') as login_id,'%s' as person_id_change,'%s' as user_name_change,b.user_group_change
                                    ,a.agency_name as user_node_change,'%s' as email_change,
                                    '%s' as phoneno_change
                                    from (select '%s'::text as per_id,agency_name,agency_id from sys_agency_info) a
                                    left join (select '%s' as per_id,array_to_string(array_agg(user_group_name),',') as user_group_change from sys_user_group
                                                where user_group_id in (select unnest (array%s))
                                                group by per_id) b on a.per_id = b.per_id
                                    where a.agency_id = '%s'""" %(data['person_id'],data['person_id'],data['person_name'],data['email'],
                                    data['mobile_phone'],data['person_id'],data['person_id'],json.dumps(data['user_group_id']).replace("\"","'"),data['node_id']))
            g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({
            "status": "E0",
            'msg': '修改用户失败',
            's': str(e)
        })



# 删除用户
@app.route('/api/sync/user/delete', methods=['POST'])
def stop_login():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            person_id=['用户id', V.required(), V.maxlength(30)], r_person=['记录人', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """select person_id from sys_login where person_id =%(person_id)s and state = '1'""",
            (data))
        person_id = g.cursor.fetchone()
        if person_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'person_id',
                    'message ': '不存在'
                }
            })
        """校验记录人ID"""
        g.cursor.execute("""select person_id,person_name
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        r_person = g.cursor.fetchone()
        if not r_person:
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "删除用户 记录人不存在"}})
        else:
            data['r_person'] =r_person['person_name']
            g.cursor.execute(
                """update sys_person set state = '0',r_person=%(r_person)s,update_time=now() where  person_id = %(person_id)s  """,
                (data))
            g.cursor.execute(
                """update sys_login set state = '0',update_time=now() where  person_id = %(person_id)s  """,
                (data))
            g.cursor.execute(
                """delete from sys_user_theme where person_id = %(person_id)s  """,
                (data))
            g.cursor.execute("""SELECT *
                                from sys_login_group a
                                left join sys_login b on a.login_id = b.login_id
                                where b.person_id = %(person_id)s""",(data))
            user_group = g.cursor.fetchall()
            if len(user_group) > 0:
                g.cursor.execute("""INSERT into h_login_synchroniz_info (login_id,person_id_change,user_name_change,user_group_change,user_node_change,email_change,phoneno_change) 
                                        select  b.user_name as login_id,%(person_id)s as person_id_change,a.person_name as user_name_change,d.user_group_change,
                                                c.agency_name as user_node_change,a.email as email_change,a.mobile_phone as phoneno_change
                                        from sys_person a
                                        left join sys_login b on a.person_id =b.person_id
                                        left join (select %(person_id)s::text as per_id,agency_name,agency_id from sys_agency_info) c on a.agency_id = c.agency_id
                                        left join (select %(person_id)s::text as per_id,array_to_string(array_agg(a.user_group_name),',') as user_group_change from sys_user_group a
                                                left join sys_login_group b on a.user_group_id = b.user_group_id
                                                left join sys_login c on b.login_id =c.login_id
                                                where person_id = %(person_id)s) d on c.per_id = d.per_id
                                        where a.person_id = %(person_id)s""" ,(data))
                
            else:
                g.cursor.execute("""INSERT into h_login_synchroniz_info (login_id,person_id_change,user_name_change,user_node_change,email_change,phoneno_change) 
                                    select  b.user_name as login_id,%(person_id)s as person_id_change,a.person_name as user_name_change,
                                            c.agency_name as user_node_change,a.email as email_change,a.mobile_phone as phoneno_change
                                    from sys_person a
                                    left join sys_login b on a.person_id =b.person_id
                                    left join (select agency_name,agency_id from sys_agency_info) c on a.agency_id = c.agency_id
                                    where a.person_id = %(person_id)s""" ,(data))
            g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({
            "status": "E0",
            'msg': '删除用户失败',
            's':str(e)
        })


# 添加用户组
@app.route('/api/sync/group/add', methods=['POST'])
def add_group():
    try:
        form = ValidateForm(
            user_group_id=['用户ID', V.required(), V.maxlength(30)],
            user_group_name=['用户组', V.required(), V.maxlength(30)],
            role_id=['角色id', V.required()], # 数组
            r_person=['记录人', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """ select user_group_name from sys_user_group where user_group_name = %(user_group_name)s and state = '1'""",
            (data))
        user_group_name = g.cursor.fetchone()
        if user_group_name is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'user_group_name',
                    'message ': '重复'
                }
            })
        role_ids = data['role_id']
        if len(role_ids)>0:
            g.cursor.execute(
            """ select role_id from sys_role where role_id in (select unnest(array%s)) and state = '1'""" % (json.dumps(role_ids).replace("\"","'")))
            role_id = g.cursor.fetchall()
        
            if len(data['role_id']) != len(role_id):
                return json.dumps({
                    'status': 'E3',
                    'msg': {
                        'errorKey': 'role_id',
                        'message ': '不存在'
                    }
                })
        """校验记录人ID"""
        g.cursor.execute("""select person_id,person_name
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        r_person = g.cursor.fetchone()
        if not r_person:
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "添加用户组 记录人不存在"}})
        else:
            data['r_person'] =r_person['person_name']
            g.cursor.execute(
                """insert into sys_user_group(user_group_name,user_group_id,state,r_person,data_source,update_time)
                                values('%s','%s','1','%s','0',now())""" %
                (data['user_group_name'], data['user_group_id'],
                data['r_person']))
            role_id = data['role_id']
            if len(role_id)>0:
                g.cursor.execute(
                """insert into sys_usergroup_role(user_group_id,role_id)
                                select user_group_id,role_id from sys_user_group,sys_role
                                where user_group_id='%s' and role_id in (select unnest(array%s))""" %
                (data['user_group_id'], json.dumps(role_id).replace("\"","'")))
                g.cursor.execute("""INSERT into h_usergroup_synchroniz_info(user_group_id,user_group_name_change,role_group_change,user_group_count_change)
                                    select '%s' as user_group_id,'%s' as user_group_name_change,
                                    array_to_string(array_agg(a.role_name),',') as role_group_change,
                                    (select coalesce(count(1),0) from sys_login_group a left join sys_login b on a.login_id = b.login_id where a.user_group_id ='%s' and b.state='1')
                                    from sys_role a
                                    where role_id in (select unnest (array%s))
                                    group by user_group_id,user_group_name_change""" % (data['user_group_id'],data['user_group_name'],data['user_group_id'],json.dumps(role_id).replace("\"","'")))
                g.conn.commit()
            else:
                g.cursor.execute("""INSERT into h_usergroup_synchroniz_info(user_group_id,user_group_name_change,user_group_count_change)
                                    values('%s','%s',(select coalesce(count(1),0) from sys_login_group a
                                    left join sys_login b on a.login_id = b.login_id where a.user_group_id ='%s' and b.state='1'))""" % (data['user_group_id'],data['user_group_name'],data['user_group_id']))
                g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({
            "status": "E0",
            'msg': '添加用户组失败',
        })


# 修改用户组
@app.route('/api/sync/group/update', methods=['POST'])
def update_group():
    try:
        form = ValidateForm(
            user_group_name=['用户组', V.required(), V.maxlength(30)],
            role_id=['角色id', V.required()], # 数组
            user_group_id=['用户组ID', V.required(), V.required(30)],
            r_person=['记录人', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """ select user_group_id from sys_user_group where user_group_id = %(user_group_id)s and state = '1'""",
            (data))
        user_group_id = g.cursor.fetchone()
        g.cursor.execute(
            """ select user_group_name from sys_user_group where user_group_name = %(user_group_name)s and state = '1' and user_group_id != %(user_group_id)s""",
            (data))
        user_group_name = g.cursor.fetchone()
        if user_group_name is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'user_group_name',
                    'message ': '重复'
                }
            })
        role_ids = data['role_id']
        if len(role_ids)>0:
            g.cursor.execute(
            """ select role_id from sys_role where role_id in (select unnest(array%s)) and state = '1'"""% (json.dumps(role_ids).replace("\"","'")))
            role_id = g.cursor.fetchall()
        
            if len(data['role_id']) != len(role_id):
                return json.dumps({
                    'status': 'E3',
                    'msg': {
                        'errorKey': 'role_id',
                        'message ': '不存在'
                    }
                })
        if user_group_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'user_group_id',
                    'message ': '不存在'
                }
            })
        g.cursor.execute(
            """delete from sys_usergroup_role where user_group_id = %(user_group_id)s""",
            (data))
        """校验记录人ID"""
        g.cursor.execute("""select person_id,person_name
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        r_person = g.cursor.fetchone()
        if not r_person:
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "修改用户组 记录人不存在"}})
        else:
            data['r_person'] =r_person['person_name']
            g.cursor.execute(
                """update sys_user_group set user_group_name = %(user_group_name)s,data_source = '0',r_person=%(r_person)s,update_time = now() where user_group_id = %(user_group_id)s""",
                (data))
            role_id = data['role_id']
            if len(role_id)>0:
                g.cursor.execute(
                """insert into sys_usergroup_role(user_group_id,role_id)
                                select user_group_id,role_id from sys_user_group,sys_role
                                where user_group_id='%s' and role_id in (select unnest(array%s))""" %
                (data['user_group_id'], json.dumps(role_id).replace("\"","'")))
                g.cursor.execute("""INSERT into h_usergroup_synchroniz_info(user_group_id,user_group_name_change,role_group_change,user_group_count_change)
                                    select '%s' as user_group_id,'%s' as user_group_name_change,
                                    array_to_string(array_agg(a.role_name),',') as role_group_change,
                                    (select coalesce(count(1),0) from sys_login_group a left join sys_login b on a.login_id = b.login_id where a.user_group_id ='%s' and b.state='1')
                                    from sys_role a
                                    where role_id in (select unnest (array%s))
                                    group by user_group_id,user_group_name_change""" % (data['user_group_id'],data['user_group_name'],data['user_group_id'],json.dumps(role_id).replace("\"","'")))
                g.conn.commit()
            else:
                g.cursor.execute("""INSERT into h_usergroup_synchroniz_info(user_group_id,user_group_name_change,user_group_count_change)
                                    values('%s','%s',(select coalesce(count(1),0) from sys_login_group a
                                    left join sys_login b on a.login_id = b.login_id where a.user_group_id ='%s' and b.state='1'))""" % (data['user_group_id'],data['user_group_name'],data['user_group_id']))
                g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({
            "status": "E0",
            'msg': '修改用户组失败',
        })


# 删除用户组
@app.route('/api/sync/group/delete', methods=['POST'])
def del_group():
    try:
        form = ValidateForm(
            user_group_id=['用户组', V.required(), V.maxlength(30)],
            r_person=['记录人', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """ select user_group_id from sys_user_group where user_group_id = %(user_group_id)s""",
            (data))
        user_group_id = g.cursor.fetchone()
        if user_group_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'user_group_id',
                    'message ': '不存在'
                }
            })
        """校验记录人ID"""
        g.cursor.execute("""select person_id,person_name
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        r_person = g.cursor.fetchone()
        if not r_person:
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "删除用户组 记录人不存在"}})
        else:
            data['r_person'] =r_person['person_name']
            g.cursor.execute(
                """update  sys_user_group set state = '0',r_person=%(r_person)s ,update_time = now() where user_group_id = %(user_group_id)s """,
                (data))
            g.cursor.execute("""SELECT user_group_name from sys_user_group where user_group_id = %(user_group_id)s""",(data))
            data['user_group_name']=g.cursor.fetchone()['user_group_name']
            g.cursor.execute("""SELECT * from sys_usergroup_role where user_group_id = %(user_group_id)s""",(data))
            user_funcs = g.cursor.fetchall()

            if len(user_funcs)>0:
                g.cursor.execute("""INSERT into h_usergroup_synchroniz_info(user_group_id,user_group_name_change,role_group_change,user_group_count_change)
                                    select %(user_group_id)s as user_group_id,%(user_group_name)s as user_group_name_change,
                                    array_to_string(array_agg(a.role_name),',') as role_group_change,
                                    (select coalesce(count(1),0) from sys_login_group a left join sys_login b on a.login_id = b.login_id where a.user_group_id =%(user_group_id)s and b.state='1')
                                    from sys_role a
                                    where role_id in (select role_id from sys_usergroup_role where user_group_id = %(user_group_id)s)
                                    group by user_group_id,user_group_name_change""" , (data))
            else:
                g.cursor.execute("""INSERT into h_usergroup_synchroniz_info(user_group_id,user_group_name_change,user_group_count_change)
                                    values('%s','%s',(select coalesce(count(1),0) from sys_login_group a
                                    left join sys_login b on a.login_id = b.login_id where a.user_group_id ='%s' and b.state='1'))""" % (data['user_group_id'],data['user_group_name'],data['user_group_id']))
            g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({
            "status": "E0",
            'msg': '删除用户组失败',
        })
